﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace sgsBase
{
	/// <summary>
	/// Base class for all database interface classes.
	/// </summary>
    [Serializable]public class DbObject : DbViewObject
    {
		public bool ExcludeNullFields = false; // exclude fields from update if their value is null.

		// almost all functionality is in the parent object. 
		// this object gives insert and update abilities, which views should not use.
        public DbObject()
        {
        }

		~DbObject()
		{
		}
		/// <summary>
		/// Insert a new data row in the table
		/// </summary>
		/// <returns></returns>
		/// the key for the tables using this insert must have an identity field as the primary key.
		/// if there is more than one primary key for the table, then a different stored procedure is called to insert the data
		/// 
        public int Insert()
        {
			if (_columns.PrimaryKeyCount > 1)
				return InsertXr();

            int ret = 0;
            string cols = "";
            string vals = "";

			_columns.GetNamesAndVals(ref cols, ref vals);
			//_columns.ForEach(delegate(DbColumn dbc)
			//{
			//    if (dbc.PrimaryKey == false || dbc.AutoIncrement == false)
			//    {
			//        if (dbc.ColumnName.Contains(" "))
			//            cols += "[" + dbc.ColumnName + "],";
			//        else
			//            cols += dbc.ColumnName + ",";
			//        //cols += dbc.ColumnName + ",";
			//        vals += dbc.ConvertValue()+",";
			//    }
			//});

			//cols = cols.TrimEnd(',');
			//vals = vals.TrimEnd(',');

            Sql = "insert into " + Table + "(" + cols + ") values(" + vals + ")";
            dbi.SpNew("spAppInsert");
			Sql = Sql.Replace("&amp;", "&");
			dbi.SpAddParam("@sql", SqlDbType.Text, Sql);
			dbi.IgnoreError = IgnoreError;

            DataTable dt = dbi.SpRun();

			Success = true;
			if (dt.Rows.Count > 0)
			{
				ret = CASTHelper.Integer(dt.Rows[0][0]);
			}

            // if there is only 1 column as primary key, then use it to set the proper column
			_columns.IncrementPK(ret);

            return ret;
        }

		public int InsertXr()
		{
			string cols = "";
			string vals = "";

			_columns.GetNamesAndVals(ref cols, ref vals);
			//m_columns.ForEach(delegate(DbColumn dbc)
			//{

			//    if (dbc.ColumnName.Contains(" "))
			//        cols += "[" + dbc.ColumnName + "],";
			//    else
			//        cols += dbc.ColumnName + ",";
			//    //cols += dbc.ColumnName + ",";
			//    vals += dbc.ConvertValue() + ",";
			//});

			//cols = cols.TrimEnd(',');
			//vals = vals.TrimEnd(',');

			Sql = "insert into " + Table + "(" + cols + ") values(" + vals + ")";
			dbi.SpNew("spAppInsertXr");
			dbi.SpAddParam("@sql", SqlDbType.VarChar, Sql);
			dbi.IgnoreError = IgnoreError;
			dbi.SpRunNon();

			Success = true;
			return 0;
		}

        /// <summary>
        /// chgecks primary key columns to see if the row already exists
        /// </summary>
        /// <returns></returns>
		public int InsertUpdate()
		{
			//string where = "";
			string sql = "";

            string where = _columns.LimitingValues(true);

			if (where != "")
			{
				sql = "select count(*) as NumRows from " + _table + " where " + where;

				int val = CASTHelper.Integer(dbi.QueryScalar(sql));
				if (val > 0)
				{
					Update(where, true);
					return 1;
				}
			}

			return Insert();
		}


		/// <summary>
		/// This version of the function provides the ability to update multiple rows
		/// within a key set by omitting keys that are blank
		/// </summary>
		/// <param name="multiRows"></param>
		/// <returns></returns>
		public bool Update()
		{
			return Update("", false);
		}
		public bool Update(bool multiRows)
		{
			return Update("", multiRows);
		}
        public bool Update(string whereClause="", bool multiRows=false)
        {
            string upds = "";
            string where = "";
            string sql = "";

			if (whereClause != "")
				where = whereClause;
			else
			{
				where = _columns.LimitingValues(true, multiRows);
			}
			upds = _columns.GetValuePairs();

			//m_columns.ForEach(delegate(DbColumn dbc)
			//{
			//    if (dbc.PrimaryKey == false)
			//    {
			//        string val = dbc.ConvertValue();

			//        if (!ExcludeNullFields || val != "NULL")
			//        {
			//            if (dbc.ColumnName.Contains(" "))
			//                upds += "[" + dbc.ColumnName + "]=" + val + ",";
			//            else
			//                upds += dbc.ColumnName + "=" + val + ",";
			//        }
			//    }
			//    else
			//    {
			//        if (!multiRows || dbc.Value != "")
			//        {
			//            if (dbc.ColumnName.Contains(" "))
			//                where += " and [" + dbc.ColumnName + "]=" + dbc.ConvertValue();
			//            else
			//                where += " and " + dbc.ColumnName + "=" + dbc.ConvertValue();
			//        }
			//    }
			//});

			//upds = upds.TrimEnd(',');
			//if (whereClause != "")
			//    where = whereClause;
			//else
			//    where = where.Substring(4);

            sql = "update " + _table + " set " + upds + " where " + where;
            
			bool ret = dbi.NonQuery(sql);
			Success = true;
			return ret;
        }

        public void Delete()
        {
            Delete("");
        }
        public void Delete(string whereClause)
        {
            string sql = "";
            if (whereClause == "")
            {
				whereClause = _columns.LimitingValues();
				//m_columns.ForEach(delegate(DbColumn dbc)
				//{
				//    if (dbc.PrimaryKey || (m_pkCount == 0 && dbc.Value != "")) // allow any fields to be set where there is no primary key
				//    {
				//        if (dbc.ColumnName.Contains(" "))
				//            whereClause += " and [" + dbc.ColumnName + "]=" + dbc.ConvertValue();
				//        else
				//            whereClause += " and " + dbc.ColumnName + "=" + dbc.ConvertValue();
				//    }
				//});
				//whereClause = whereClause.Substring(4);
            }
            sql = "delete from  " + _table + " where " + whereClause;
            dbi.NonQuery(sql);
			Success = true;
           // ClearValues();
            return;
        }

		public int GetNextSeqNo(string column, string whereClause)
		{
			string sql = "select max(ISNULL(" + column + ",0))+ 1 as SeqNo from " + _table;
			sql += " where " + whereClause;

			DataTable dt = dbi.Query(sql);

			return CASTHelper.Integer(dt.Rows[0][0]);
		}

    }
}
